﻿create procedure [dbo].[sp_GiuCho]
@DSHangGhe varchar(max),
@SLHangGhe varchar(max),
@DSLoaiKH varchar(max),
@SLLoaiKH varchar(max),
@SanBayDi varchar(10),
@SanBayDen varchar(10),
@ThoiGianBay datetime,
@ThoiGianVe datetime,
@SoNgayGiuCho int	
as
declare 
	@SoLoaiChuyenBay int,
	@MaGiuCho int,
	@MaHangGhe int,
	@SoLuong int,
	@MaChuyenBay int,
	@LoaiChuyenBay int
declare
	chuyenBaycur cursor for select MaChuyenBay, LoaiChuyenBay from dbo.func_GetChuyenBay(@SanBayDi,@SanBayDen,@ThoiGianBay,@ThoiGianVe)

declare	
	hangGhecur cursor for select item, item1 from dbo.udf_List2Table(@DSHangGhe, @SLHangGhe,',')
begin
	set @SoLoaiChuyenBay = (select count(LoaiChuyenBay) from dbo.func_KiemTraViTriTrong(@DSHangGhe, @SLHangGhe, @SanBayDi, @SanBayDen, @ThoiGianBay, @ThoiGianVe))
	if (@SoLoaiChuyenBay < 2)
		return 0
	begin tran
	insert into DBGiuCho([SanBayDi],[SanBayDen],[NgayBay],[NgayVe],[NgayDat],[SoNgayGiuCho],[TinhTrang]) values (@SanBayDi,@SanBayDen,@ThoiGianBay,@ThoiGianVe,GETDATE(),@SoNgayGiuCho,'da giu cho')
	if @@ERROR <> 0
	begin
		rollback tran
		return 0
	end
	set @MaGiuCho = (select max(MaGiuCho) from dbo.DBGiuCho);

	insert into dbo.CTGiuChoLoaiKhachHang select @MaGiuCho, item, item1 from dbo.udf_List2Table(@DSLoaiKH, @SLLoaiKH,',')
	if @@ERROR <> 0
	begin
		rollback tran
		return 0
	end
	insert into dbo.CTGiuChoHangGhe select @MaGiuCho, item, item1 from dbo.udf_List2Table(@DSHangGhe, @SLHangGhe,',')
	if @@ERROR <> 0
	begin
		rollback tran
		return 0
	end
	open chuyenBaycur
		fetch next from chuyenBaycur into  @MaChuyenBay, @LoaiChuyenBay
		while(@@FETCH_STATUS = 0)
		begin
			open hangGhecur
				fetch next from hangGhecur into  @MaHangGhe, @SoLuong
				while(@@FETCH_STATUS = 0)
				begin
					update DanhSachHangGhe set SoViTriConTrong = SoViTriConTrong - @SoLuong where MaHangGhe = @MaHangGhe and MaChuyenBay = @MaChuyenBay
					if @@ERROR <> 0
					begin
						close chuyenBaycur	
						deallocate chuyenBaycur
						close hangGhecur
						deallocate hangGhecur
						rollback tran
						return 0
					end
				fetch next from hangGhecur into  @MaHangGhe, @SoLuong
				end
			close hangGhecur
			insert into ChuyenBayGiuCho([MaGiuCho],[MaChuyenBay],[LoaiChuyenBay]) values(@MaGiuCho,@MaChuyenBay,@LoaiChuyenBay)
			if @@ERROR <> 0
			begin
				close chuyenBaycur	
				deallocate chuyenBaycur
				rollback tran
				return 0
			end
			fetch next from chuyenBaycur into  @MaChuyenBay, @LoaiChuyenBay
		end
	close chuyenBaycur	
	deallocate chuyenBaycur
	deallocate hangGhecur
	commit tran
	return @MaGiuCho
end


